﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Collections;
using Teradata.Client.Provider;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

/// <summary>
/// Properties needs a STATE col, a PROP_TRAC_NAME, TRAC_PROP_NAME, a GM col
/// </summary>
public class Properties
{

    private DataView _dvProperties;

    public Properties()
    {

    }

    public Properties(string psDud)
    {
    }

    private DataView Select(string psWhere)
    {

        //_dvProperties = SQLServer.GetDataTable("SCORE_ACC_PROPERTIES.PROPERTIES",psWhere).DefaultView;
        _dvProperties = SQLServer.GetDataTable("dbo.PROPERTIES_WITH_SALES_VIEW",psWhere).DefaultView;
        _dvProperties.RowFilter = "";

        if (!_dvProperties.Table.Columns.Contains("PROPERTY"))
        {
            _dvProperties.Table.Columns.Add("PROPERTY");
        }

        if (((RollupMetadata)HttpContext.Current.Session["RollupMetadata"]).ByTracId)
        {
            _dvProperties.Sort = "TRAC_ID";
            _dvProperties.Table.Columns["PROPERTY"].Expression = "TRIM(TRAC_ID) + ' - ' + PROPERTY_NM";
        }
        else
        {
            _dvProperties.Sort = "PROPERTY_NM";
            _dvProperties.Table.Columns["PROPERTY"].Expression = "PROPERTY_NM +' (' + TRIM(TRAC_ID) + ')'";
        }

        DataView dv = _dvProperties;

        ArrayList alRowFilters = (new Menus()).CreateRowFilter("Properties", false);
        alRowFilters.Add(psWhere);
        string sRowFilter = string.Empty;
        if (alRowFilters.Count > 0)
        {
            sRowFilter = string.Join(" AND ", (alRowFilters.ToArray(typeof(string)) as string[]));
            _dvProperties.RowFilter = sRowFilter;
        }
        dv.RowFilter = sRowFilter;

        return dv;

    }

    private DataView Select(string psWhere, string psOrderBy)
    {
        DataView dv = Select(psWhere);
        dv.Sort = psOrderBy;

        return dv;
    }

    public DataView ByAreaMgr(string psAreaMgr)
    {
        return Select("AREA_MGR='" + psAreaMgr.Replace("'", "''") + "'");
    }

    public DataView ByAcctMgr(string psAcctMgr)
    {
        return Select("ACCT_MGR='" + psAcctMgr.Replace("'", "''") + "'");
    }

    public DataView ByMarket(string psMarket)
    {
        return Select("MARKET='" + psMarket + "'");
    }

    public DataView ByState(string psState)
    {
        if (psState == "ZZ")
            return Select("STATE_CD LIKE'%'");
        else
            //CREATE A STATE COL FROM TRAC ID OR USE SUBSTR?
            return Select("STATE_CD='" + psState + "'");
    }

    public DataView ByPortfolio(string psPortfolio)
    {
        return Select("PORTFOLIO_NM='" + psPortfolio.Replace("'", "''") + "'");
    }

    public DataView All()
    {
        return _dvProperties;
    }

    public DataView ByGenMgr(string psGenMgr)
    {
        //JOIN GM TO YOUR GLOBAL DV
        return Select("REGION='Southeast'");
    }

   public DataView ByRegion(string psRegion)
         {
        //JOIN GM TO YOUR GLOBAL DV
             string sFilter = string.Format("REGION='{0}'", psRegion.Trim()).Replace("='ZZ'", " LIKE '%'");
        return Select(sFilter);
    }
    public DataView ByTracID(string psTracID)
    {
        return Select("TRAC_ID = '" + psTracID + "'");
    }

    public DataView SumByAreaMgr(string psAreaMgr)
    {
        string sRowFilter = "AREA_MGR='" + psAreaMgr.Replace("'", "''") + "'";

        DataTable dt = new DataTable();
        DataView dv = Select(sRowFilter);
        dt = dv.Table.Copy();
        DataView dv2 = dt.DefaultView;
        dv2[0]["PROPERTY_NM"] = psAreaMgr;
        dv2[0]["AREA_MGR"] = psAreaMgr;
        dv2[0]["ACCT_MGR"] = "";
        dv2[0]["PORTFOLIO_NM"] = "";
        if (dv.Count > 0)
            dv2[0]["STATE_CD"] = dv[0]["STATE_CD"];
        SumProperties(sRowFilter, dv2);
        return dv2;
    }

    public DataView SumByAcctMgr(string psAcctMgr)
    {
        string sRowFilter = "ACCT_MGR='" + psAcctMgr.Replace("'", "''") + "'";
        DataTable dt = new DataTable();
        DataView dv = Select(sRowFilter);
        dt = dv.Table.Copy();
        DataView dv2 = dt.DefaultView;
        dv2[0]["PROPERTY_NM"] = psAcctMgr;
        if (dv.Count > 0) dv2[0]["AREA_MGR"] = dv[0]["AREA_MGR"];// dt.Compute("DISTINCT COUNT(AREA_MGR)", sRowFilter);
        dv2[0]["ACCT_MGR"] = psAcctMgr;
        dv2[0]["PORTFOLIO_NM"] = "";// dt.Compute("DISTINCT COUNT(PORTFOLIO_NM)", sRowFilter);
        if (dv.Count > 0) dv2[0]["STATE_CD"] = dv[0]["STATE_CD"];
        SumProperties(sRowFilter, dv2);
        return dv2;
    }

    public DataView SumByPortfolio(string psPortfolio)
    {
        string sRowFilter = "PORTFOLIO_NM='" + psPortfolio.Replace("'", "''") + "'";

        DataTable dt = new DataTable();
        DataView dv = Select(sRowFilter);
        if (dv.Count == 0)
        {
            System.Web.HttpContext.Current.Response.Flush();
            return dv;
        }
        dt = dv.Table.Copy();
        DataView dv2 = dt.DefaultView;
        dv2[0]["PROPERTY_NM"] = psPortfolio;
        if (dv.Count > 0)
        {
            dv2[0]["AREA_MGR"] = dv[0]["AREA_MGR"];// dt.Compute("DISTINCT COUNT(AREA_MGR)", sRowFilter);
            dv2[0]["ACCT_MGR"] = dv[0]["ACCT_MGR"];// dt.Compute("DISTINCT COUNT(ACCT_MGR)", sRowFilter);
            dv2[0]["STATE_CD"] = dv[0]["STATE_CD"];
            dv2[0]["PORTFOLIO_NM"] = dv[0]["PORTFOLIO_NM"];
        }
        SumProperties(sRowFilter, dv2);
        return dv2;
    }

    public DataView SumByState(string psStateCd)
    {
        string sRowFilter = "STATE_CD='" + psStateCd.Replace("'", "''") + "'";

        DataTable dt = new DataTable();
        DataView dv = Select(sRowFilter);
        dt = dv.Table.Copy();
        DataView dv2 = dt.DefaultView;
        dv2[0]["PROPERTY_NM"] = psStateCd;
        dv2[0]["AREA_MGR"] = "";
        dv2[0]["AREA_MGR"] = "";// dt.Compute("DISTINCT COUNT(AREA_MGR)", sRowFilter);
        dv2[0]["ACCT_MGR"] = "";// dt.Compute("DISTINCT COUNT(ACCT_MGR)", sRowFilter);
        dv2[0]["STATE_CD"] = psStateCd;
        dv2[0]["PORTFOLIO_NM"] = "";
        SumProperties(sRowFilter, dv2);
        return dv2;
    }

    public DataView SumByMarket(string psMarket)
    {
        string sRowFilter = "MARKET='" + psMarket.Replace("'", "''") + "'";

        DataTable dt = new DataTable();
        DataView dv = Select(sRowFilter);
        dt = dv.Table.Copy();
        DataView dv2 = dt.DefaultView;
        dv2[0]["PROPERTY_NM"] = psMarket;
        dv2[0]["AREA_MGR"] = dv[0]["AREA_MGR"];// dt.Compute("DISTINCT COUNT(AREA_MGR)", sRowFilter);
        dv2[0]["ACCT_MGR"] = "";// dt.Compute("DISTINCT COUNT(ACCT_MGR)", sRowFilter);
        if (dv.Count > 0) dv2[0]["STATE_CD"] = dv[0]["STATE_CD"];
        dv2[0]["PORTFOLIO_NM"] = "";
        SumProperties(sRowFilter, dv2);
        return dv2;
    }
    public DataView SumByRegion(string psRegion)
    {

        DataTable dt = new DataTable();
        DataView dv = Select("");
        dt = dv.Table.Copy();
        DataView dv2 = dt.DefaultView;
        dv2[0]["PROPERTY_NM"] = psRegion;
        dv2[0]["AREA_MGR"] = "";
        dv2[0]["AREA_MGR"] = "";// dt.Compute("DISTINCT COUNT(AREA_MGR)", sRowFilter);
        dv2[0]["ACCT_MGR"] = "";// dt.Compute("DISTINCT COUNT(ACCT_MGR)", sRowFilter);
        dv2[0]["STATE_CD"] = "";
        dv2[0]["PORTFOLIO_NM"] = "";
        SumProperties("", dv2);
        return dv2;
    }

    private static void SumProperties(string sRowFilter, DataView dv)
    {

        dv[0]["TRAC_ID"] = "";
        dv[0]["ADDRESSES"] = dv.Table.Compute("SUM(ADDRESSES)", sRowFilter);
        dv[0]["GREENS"] = dv.Table.Compute("SUM(GREENS)", sRowFilter);
        dv[0]["SUBSCRIBERS"] = dv.Table.Compute("SUM(SUBSCRIBERS)", sRowFilter);
        dv[0]["IPTV"] = dv.Table.Compute("SUM(IPTV)", sRowFilter);
        dv[0]["HSIA"] = dv.Table.Compute("SUM(HSIA)", sRowFilter);
        dv[0]["VOIP"] = dv.Table.Compute("SUM(VOIP)", sRowFilter);
        dv[0]["TOTAL_REVENUE"] = dv.Table.Compute("SUM(TOTAL_REVENUE)", sRowFilter);
        dv[0]["IPTV_REVENUE"] = dv.Table.Compute("SUM(IPTV_REVENUE)", sRowFilter);
        dv[0]["HSIA_REVENUE"] = dv.Table.Compute("SUM(HSIA_REVENUE)", sRowFilter);
        dv[0]["VOIP_REVENUE"] = dv.Table.Compute("SUM(VOIP_REVENUE)", sRowFilter);
        dv[0]["OTHER_UVERSE_REVENUE"] = dv.Table.Compute("SUM(OTHER_UVERSE_REVENUE)", sRowFilter);
        dv[0]["LIT"] = dv.Table.Compute("AVG(LIT)", sRowFilter);
        dv[0]["INSERVICE"] = dv.Table.Compute("AVG(INSERVICE)", sRowFilter);
        //dv[0]["SATURATION"] = dv.Table.Compute("AVG(100*GREENS /(SUBSCRIBERS + 0.01))", sRowFilter);
        dv[0]["SATURATION"] = dv.Table.Compute("AVG(SATURATION)", sRowFilter);
        dv[0]["IPTVPercentage"] = dv.Table.Compute("AVG(IPTVPercentage)", sRowFilter);
        dv[0]["HSIAPercentage"] = dv.Table.Compute("AVG(HSIAPercentage)", sRowFilter);
        dv[0]["VOIPPercentage"] = dv.Table.Compute("AVG( VOIPPercentage)", sRowFilter);
        dv[0]["OtherPercentage"] = dv.Table.Compute("AVG(OtherPercentage)", sRowFilter);
        dv[0]["TotalPerSubscriber"] = dv.Table.Compute("AVG(TotalPerSubscriber)", sRowFilter);
        dv[0]["IPTVPerSubscriber"] = dv.Table.Compute("AVG(IPTVPerSubscriber)", sRowFilter);
        dv[0]["HSIAPerSubscriber"] = dv.Table.Compute("AVG(HSIAPerSubscriber)", sRowFilter);
        dv[0]["VOIPPerSubscriber"] = dv.Table.Compute("AVG(VOIPPerSubscriber)", sRowFilter);
        dv[0]["OtherPerSubscriber"] = dv.Table.Compute("AVG( OtherPerSubscriber)", sRowFilter);
    }

    public String PropertiesDate
    {
        get
        {
            if (HttpContext.Current.Session["PropertiesDate"] == null)
                return "Juvember, 2020";
            else
                return HttpContext.Current.Session["PropertiesDate"].ToString();
        }
    }

    public void GetPropertiesLatestDate()
    {
        using (TdConnection cn = new TdConnection(ConfigurationManager.ConnectionStrings["TDConnString"].ConnectionString))
        {
            try
            {
                String SQL = "SELECT TOP 1 ASOF_DATE FROM SUNRISE_TEST.ACC_BASE_DATA";
                cn.Open();
                using (TdCommand cm = new TdCommand(SQL, cn))
                {
                    cm.CommandTimeout = 0;
                    using (TdDataReader dr = cm.ExecuteReader())
                        while (dr.Read())
                        {
                            System.Web.HttpContext.Current.Application["PropertiesDate"] = String.Format("{0:MMMM} {0:yyyy}", dr.GetDateTime(0));
                            System.Web.HttpContext.Current.Session["Connected"] = true;
                        }
                }
            }
            catch (Exception ex)
            {
                System.Web.HttpContext.Current.Application["Exception"] = ex;
            }
        }
    }

}